package com.hoas.comparedb.mysql;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.StrUtil;
import com.hoas.comparedb.core.DBReader;
import com.hoas.comparedb.core.DBWriter;
import com.hoas.comparedb.entity.Table;
import com.hoas.comparedb.entity.TableColumn;
import com.hoas.comparedb.entity.TableIndex;
import com.hoas.comparedb.pojo.TableChange;
import com.hoas.comparedb.pojo.TableColumnDiffItem;
import com.hoas.comparedb.pojo.TableDiffItem;
import com.hoas.comparedb.pojo.TableIndexDiffItem;
import com.hoas.comparedb.util.CompareReporter;
import org.thymeleaf.TemplateEngine;
import org.thymeleaf.context.Context;

import java.io.IOException;
import java.io.Writer;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class MySQLWriter implements DBWriter {

    private static String lineSeparator = System.getProperty("line.separator", "\n");


    private Writer writer;

    private Properties properties;


    public MySQLWriter(Writer writer, Properties properties) {
        this.writer = writer;
        this.properties = properties;
    }

    /**
     * @throws Exception
     */
    @Override
    public void write(
            DBReader soReader,
            DBReader snReader,
            List<TableChange> tableChanges,
            CompareReporter compareReporter,
            String type
    ) throws Exception {

        try {
            if ("sql".equalsIgnoreCase(type)) {
                genSql(soReader, snReader, compareReporter, tableChanges);
            }
            else if ("html".equalsIgnoreCase(type)) {
                genHtml(soReader, snReader,compareReporter, tableChanges );
            }

        } catch (Exception e) {
            e.printStackTrace();
        }



    }

    @Override
    public void close() throws IOException {
        writer.close();;
    }

    private void genHtml(DBReader soReader, DBReader snReader, CompareReporter compareReporter, List<TableChange> tableChanges) throws IOException {

        TemplateEngine templateEngine = TemplateHelper.getTemplateEngine();

        //构造上下文(Model)
        Context context = new Context();
        context.setVariable("so_db", soReader.getSource());
        context.setVariable("sn_db", snReader.getSource());
        context.setVariable("date", DateUtil.today());
        context.setVariable("compareReporter",compareReporter);
        context.setVariable("tableChanges", tableChanges);
        //渲染模板

        templateEngine.process("mysql_diff", context, writer);



    }

    private void genSql(
            DBReader soReader,
            DBReader snReader,
            CompareReporter compareReporter,
            List<TableChange> tableChanges
    ) throws IOException {
        List<String> sqls = new ArrayList<>();
        sqls.add("/* ");
        sqls.add(" * so: " + soReader.getSource() + " => sn: " + snReader.getSource());
        sqls.add(" * " + compareReporter.toString());
        sqls.add(" * ");
        sqls.add(" * @date "+ DateUtil.today());
        sqls.add(" */");

        sqls.add("use "+ snReader.getDb() +";");

        sqls.add("/* CREATE TABLES */");
        tableChanges.stream().filter( r -> r.getTableDiff() == null || TableDiffItem.CREATE.equals(r.getTableDiff().getType())).forEach(tb -> genCreateTableSql( soReader, tb, sqls ) );

        sqls.add( lineSeparator );

        sqls.add("/* ALTER TABLES */");
        tableChanges.stream().filter( r -> r.getTableDiff() != null && TableDiffItem.ALTER.equals(r.getTableDiff().getType())).forEach(tb -> genAlterTableSql( tb, sqls ) );

        for (String line : sqls) {
            writer.write( line + lineSeparator );
        }

    }

    private void genCreateTableSql(DBReader soReader, TableChange tableChange, List<String> sqls) {
        sqls.add(String.format("-- %s.%s",  tableChange.getTable().getTableSchema() , tableChange.getTable().getTableName()));
        try {
            String sql = soReader.getTableStructure( tableChange.getTable() );
            sqls.add(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        sqls.add(";" + lineSeparator);

    }

    private List<String> genAlterTableSql( TableChange tableChange, List<String> sqls) {
        List<String> col_sqls = new ArrayList<>();
        sqls.add(String.format("-- %s.%s",  tableChange.getTable().getTableSchema() , tableChange.getTable().getTableName()));

        if (tableChange.getColumnDiffs() != null) {


            tableChange.getColumnDiffs().forEach((v) -> {

                TableColumn nt = v.getNt();
                TableColumn ot = v.getOt();

                switch (v.getType()) {
                    case TableColumnDiffItem.ADD:
                        // 添加字段
                        col_sqls.add("ADD COLUMN `" + nt.getColumnName() + "`"
                                + charsetColumn( tableChange.getTable(), nt, ot ));
                        break;
                    case TableColumnDiffItem.DROP:
                        // 删除字段
                        col_sqls.add("DROP COLUMN `" + ot.getColumnName() + "`");
                        break;
                    case TableColumnDiffItem.MODIFY:
                        // 修改字段
                        col_sqls.add("MODIFY COLUMN `" + nt.getColumnName() + "`"
                                + charsetColumn( tableChange.getTable(), nt, ot ));
                        break;
                }

            });

        }

        if (tableChange.getIndexDiffs() != null) {


            tableChange.getIndexDiffs().forEach((v) -> {

                TableIndex nt = v.getNt();
                TableIndex ot = v.getOt();

                switch (v.getType()) {
                    case TableIndexDiffItem.ADD:
                        // 添加索引
                        if ("PRIMARY".equals(nt.getIndexName())) {

                        } else if(nt.getNonUnique() == 0) {
                            col_sqls.add(String.format("ADD UNIQUE INDEX %s ( %s )", nt.getIndexName(), nt.getCompositIndex()));
                        } else {
                            col_sqls.add(String.format("ADD INDEX %s ( %s )", nt.getIndexName(), nt.getCompositIndex()));
                        }
                        break;
                    case TableIndexDiffItem.DROP:
                        // 删除索引
                        col_sqls.add("DROP INDEX " +  ot.getIndexName());
                        break;
                    case TableIndexDiffItem.MODIFY:
                        col_sqls.add("DROP INDEX " +  nt.getIndexName());

                        // 添加索引
                        if ("PRIMARY".equals(nt.getIndexName())) {

                        } else if(nt.getNonUnique() == 0) {
                            col_sqls.add(String.format("ADD UNIQUE INDEX %s ( %s )", nt.getIndexName(), nt.getCompositIndex()));
                        } else {
                            col_sqls.add(String.format("ADD INDEX %s ( %s )", nt.getIndexName(), nt.getCompositIndex()));
                        }
                        break;
                }


            });

        }

        if (tableChange.getTableDiff() != null) {

            Table nt = tableChange.getTableDiff().getNt();
            Table ot = tableChange.getTableDiff().getOt();

            List<String> table_alter_sql = new ArrayList<>();
            // 表字段发生变化 CHARACTER SET = utf8mb4, COLLATE = utf8mb4_bin, COMMENT = 'MQ消息发送方持久化日志信息1';
            if (!StrUtil.equals(nt.getCharacterSetName(), ot.getCharacterSetName())) {
                table_alter_sql.add("CHARACTER SET = "+ nt.getCharacterSetName());
            }

            if (!StrUtil.equals(nt.getTableCollation(), ot.getTableCollation())) {
                table_alter_sql.add("COLLATE = "+ nt.getTableCollation());
            }

            if (!StrUtil.equals(nt.getTableComment(), ot.getTableComment())) {
                table_alter_sql.add("COMMENT = '"+ (nt.getTableComment() == null ? "" : nt.getTableComment()) +"'");
            }

            if (table_alter_sql.size() > 0) {
                col_sqls.add(StrUtil.join(", ",table_alter_sql));
            }

        }

        if (col_sqls.size() > 0) {

            sqls.add("" + String.format("ALTER TABLE %s.%s",  tableChange.getTable().getTableSchema() , tableChange.getTable().getTableName()));
            sqls.add(StrUtil.join("," + lineSeparator , col_sqls));
            sqls.add(";" + lineSeparator);

        }

        return sqls;
    }

    private static List<Table> readTables(MySQLReader reader, String tableName) throws SQLException {
        List<Table> tables = reader.getTables( tableName );
        for (Table table : tables) {
            List<TableColumn> columns = reader.getColumns( table );
            table.setTableColumns( columns );

            List<TableIndex> indexes = reader.getIndexes( table );
            table.setTableIndexes( indexes );

        }

        return tables;
    }

    private static String charsetColumn(Table tb, TableColumn nt, TableColumn ot) {

        // 添加字段 `SHORT_NAME` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '简称' AFTER `SUBSIDIARY_CODE`

        StringBuffer sb = new StringBuffer();
        if (ot == null || !StrUtil.equals(nt.getColumnType(), ot.getColumnType()) && StrUtil.isNotBlank(nt.getColumnType())) {
            sb.append(" " + nt.getColumnType());
        }

        if (ot == null || !StrUtil.equals(nt.getCharacterSetName(), ot.getCharacterSetName())
                || !StrUtil.equals(nt.getCollationName(), ot.getCollationName())) {
            if (StrUtil.isNotBlank(nt.getCharacterSetName())) {
                sb.append(" CHARACTER SET " + nt.getCharacterSetName());
                if (StrUtil.isNotBlank( nt.getCollationName() )) {
                    sb.append( " COLLATE " + nt.getCollationName());
                }
            }
        }

        if (ot == null || !StrUtil.equals(nt.getIsNullable(), ot.getIsNullable()) && StrUtil.isNotBlank( nt.getIsNullable())) {
            if ("YES".equals(nt.getIsNullable())) {
                sb.append(" NULL");
            } else {
                sb.append(" NOT NULL");
            }
        }

        if (ot == null || !StrUtil.equals(nt.getColumnDefault(), ot.getColumnDefault())) {
            if (nt.getColumnDefault() == null) {
                sb.append(" DEFAULT NULL");
            } else {
                sb.append(" DEFAULT '" + nt.getColumnDefault() + "'");
            }

        }

        if (ot == null || !nt.isAutoIncr() != ot.isAutoIncr() ) {
            if (nt.isAutoIncr()) {
                sb.append(" AUTO_INCREASE");
            }
        }

        if (ot == null || !StrUtil.equals(nt.getColumnComment(), ot.getColumnComment())) {
            sb.append(" COMMENT '" + nt.getColumnComment() + "'");
        }

        if (nt.getColumnPosition() != 1) {
            TableColumn preColumn = tb.getTableColumns().get( nt.getColumnPosition() - 2);
            sb.append(" AFTER `"+ preColumn.getColumnName() +"`");
        }

        return sb.toString();
    }

    public Properties getProperties() {
        return properties;
    }

    @Override
    public Writer getWriter() {
        return writer;
    }
}
